using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using DTO;

namespace DAO
{
   public  class PhieuMatSachDAO
    {
       public static DataTable _laydsphieumatsach_DAO()
       {
         
           string sql = "select pms.MaPhieuMatSach,s.MaSach,s.TenSach,pms.NgayGhiNhan,dg.MaDocGia,dg.HoTen,pms.TienPhat,nv.HoTen,nv.MaNhanVien from PhieuMatSach as pms,Sach as s,TheDocGia as dg,NhanVien as nv ";
           sql += " where s.MaSach=pms.MaSach and dg.MaDocGia=pms.MaDocGia and nv.MaNhanVien=pms.NguoiGhiNhan ";

           DataTable dt = DataProvider.ExcuteQuery(sql);

           return dt;
       }

       public static void ThemPhieuMatSach_DAO(PhieuMatSachDTO pmsDTO)
       {
           string sql = "insert into PhieuMatSach(MaSach,NgayGhiNhan,MaDocGia,TienPhat,NguoiGhiNhan) values (";
           sql += pmsDTO.Masach + ",'" + pmsDTO.Ngayghinhan.ToString() + "'," + pmsDTO.Madocgia.ToString() + "," + pmsDTO.Tienphat.ToString() + "," + pmsDTO.Nguoighinhan.ToString() + ")";
           DataProvider.ExecutenonQuery(sql);
       }
       public static void XoaPhieuMatSach_DAO(int maPMS)
       {
           string sql = "delete from PhieuMatSach where MaPhieuMatSach=" + maPMS.ToString();
           DataProvider.ExecutenonQuery(sql);
       }

       public static void CapNhatPhieuMatSach_DAO(PhieuMatSachDTO pmsDTO)
       {
           string sql = "update PhieuMatSach set MaSach=" + pmsDTO.Masach.ToString() + ",NgayGhiNhan='" + pmsDTO.Ngayghinhan.ToString() + "',MaDocGia=" + pmsDTO.Madocgia.ToString() + ",TienPhat=" + pmsDTO.Tienphat.ToString() + ",NguoiGhiNhan=" + pmsDTO.Nguoighinhan.ToString() + " where MaPhieuMatSach=" + pmsDTO.Maphieumatsach.ToString();
           DataProvider.ExecutenonQuery(sql);

       }

       public static DataTable TimPhieuMatSach_DAO(string param)
       {
           string[] str ={ "MaSach", "MaDocGia", "NguoiGhiNhan" };
           string sql = "select pms.MaPhieuMatSach,s.MaSach,s.TenSach,pms.NgayGhiNhan,dg.MaDocGia,dg.HoTen,pms.TienPhat,nv.HoTen,nv.MaNhanVien from PhieuMatSach as pms,Sach as s,TheDocGia as dg,NhanVien as nv ";
           sql += " where s.MaSach = pms.MaSach and dg.MaDocGia=pms.MaDocGia and nv.MaNhanVien=pms.NguoiGhiNhan ";

           sql += param;
           DataTable dt = DataProvider.ExcuteQuery(sql);

           return dt;
       }
    }
}
